import pymysql
import requests
from lxml import etree


def que(db, cursor):
    headers = {
        "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/98.0.4758.102 Safari/537.36"
    }
    sql = "select BV from bilibili"
    cursor.execute(sql)
    result = cursor.fetchall()
    for i in result:
        a = requests.get(url="https://www.bilibili.com/video/{}".format(i[0]), headers=headers)
        a.encoding = "utf-8"
        tree = etree.HTML(a.text)
        title = tree.xpath("//html/head/title/text()")[0]
        if "出错啦" in title or "视频去哪了呢" in title:
            print("视频没了~~~~~~~~~~~~~~~~~~~~~")
            print(i[0])
            delete_sql = "delete from bilibili where BV='{}'".format(i[0])
            try:
                cursor.execute(delete_sql)
                db.commit()
            except:
                db.rollback()

        else:
            print(title)


if __name__ == '__main__':
    db = pymysql.connect(
        host="localhost",
        user="root",
        password="123456",
        database="movie_cat"
    )
    cursor = db.cursor()
    que(db, cursor)
    db.close()
